package com.intretech.umsin.code.generator.provider;


import com.intretech.umsin.code.generator.component.CamelCaseUtils;
import com.intretech.umsin.code.generator.component.DataBaseType;
import com.intretech.umsin.code.generator.entity.ColumnSchema;
import com.intretech.umsin.code.generator.entity.PrimaryKeySchema;
import com.intretech.umsin.code.generator.entity.TableSchema;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * @version V1.0
 * @ClassName: MySQLSchemaProvider
 * @Description:(MySQL数据库实现)
 * @author: 李启联
 * @date: 2020-01-22 10:25
 * @Copyright: Copyright (c), 2020
 */
@Service
public class MySQLSchemaProvider implements IDbSchemaProvider {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private TemplateMethodForGetDataType getDataTypeTemplateMethod;

    @Autowired
    private TemplateMethodForUnderlineToCamel underlineToCamelTemplateMethod;

    private String databaseName = "";

    public DataBaseType dataBaseType(){
        return DataBaseType.MYSQL;
    }


    private static Logger logger = org.slf4j.LoggerFactory.getLogger(MySQLSchemaProvider.class);
    @Override
    public String getDatabaseName() {
        if (databaseName.equals("")) {
            try {
                Map<String, Object> result = jdbcTemplate.queryForMap(" SELECT DATABASE() AS DATABASE_NAME ");
                if(null != result && result.containsKey("DATABASE_NAME"))
                    databaseName = result.get("DATABASE_NAME").toString();
            } catch (Exception e) {
                logger.error("getDatabaseName",e);
            }
        }
        return databaseName;
    }

    @Override
    public List<TableSchema> getTables(String... tables) {
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        List<TableSchema> tableList = new ArrayList<>();
        String sql ="";
        BeanPropertyRowMapper<TableSchema> tableSchemaBeanPropertyRowMapper = BeanPropertyRowMapper.newInstance(TableSchema.class);
        if(tables.length <1) {
            sql = String.format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') ORDER BY 1", getDatabaseName());
            tableList = namedParameterJdbcTemplate.query(sql,tableSchemaBeanPropertyRowMapper);
        }else{
            sql = String.format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = :table_schem AND TABLE_NAME IN (:tables) AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') ORDER BY 1");
            Map<String,Object> param = new HashMap<>();
            param.put("table_schem",getDatabaseName());
            param.put("tables",Arrays.asList(tables));
            tableList = namedParameterJdbcTemplate.query(sql,param,tableSchemaBeanPropertyRowMapper);
        }

        return tableList;
    }

    @Override
    public TableSchema getTableSchema(String tableName) {
        String sql = String.format( "SELECT TABLE_NAME, '' OWNER, CREATE_TIME, IFNULL(TABLE_COMMENT,'') AS TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s' AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') ORDER BY 1", getDatabaseName(),tableName);
        BeanPropertyRowMapper<TableSchema> tableSchemaBeanPropertyRowMapper = BeanPropertyRowMapper.newInstance(TableSchema.class);
        TableSchema tableSchema = jdbcTemplate.queryForObject(sql,tableSchemaBeanPropertyRowMapper);
        return tableSchema;
    }

    @Override
    public List<ColumnSchema> getTableColumns(String tableName) {
        String sql = String.format(
                "SELECT COLUMN_NAME,\r\n" +
                        "       CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE,\r\n" +
                        "       DATA_TYPE,\r\n" +
                        "       CASE WHEN DATA_TYPE IN('CHAR', 'VARCHAR', 'BINARY', 'VARBINARY', 'NCHAR', 'NVARCHAR') THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END  `LENGTH`,\r\n" +
                        "       IFNULL(COLUMN_COMMENT,'') COLUMN_COMMENT,\r\n" +
                        "       TABLE_NAME\r\n" +
                        "FROM INFORMATION_SCHEMA.COLUMNS\r\n" +
                        "WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'\r\n" +
                        "ORDER BY ORDINAL_POSITION ",
                getDatabaseName(), tableName);
        List<ColumnSchema> colList = new ArrayList<>();
        try {
            BeanPropertyRowMapper<ColumnSchema> rm = BeanPropertyRowMapper.newInstance(ColumnSchema.class);
            colList = jdbcTemplate.query(sql,rm);
        } catch (Exception e) {
            logger.error("getTableColumns",e);
        }
        return colList;
    }

    @Override
    public List<PrimaryKeySchema> getTablePrimaryKey(String tableName) {
        String sql = String.format(
                "SELECT t1.CONSTRAINT_NAME, \r\n" +
                        "       t1.COLUMN_NAME,\r\n" +
                        "       t1.TABLE_NAME\r\n" +
                        "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1\r\n" +
                        "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2\r\n" +
                        "ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA\r\n" +
                        "AND t2.TABLE_NAME = t1.TABLE_NAME\r\n" +
                        "AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME\r\n" +
                        "WHERE t1.TABLE_SCHEMA = '%s' AND t1.TABLE_NAME = '%s'\r\n" +
                        "AND t2.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n" +
                        "ORDER BY t1.ORDINAL_POSITION",
                getDatabaseName(), tableName);
        List<PrimaryKeySchema> pkList = new ArrayList<>();
        try {
            BeanPropertyRowMapper<PrimaryKeySchema> rm = BeanPropertyRowMapper.newInstance(PrimaryKeySchema.class);
            pkList = jdbcTemplate.query(sql,rm);
        } catch (Exception e) {
            logger.error("getTablePrimaryKey",e);
        }

        if(pkList.size()<1) {//如果没有定义主键就指定该表第一个字段为主键
            List<ColumnSchema> colList = getTableColumns(tableName);
            pkList.add(new PrimaryKeySchema(colList.get(0).getColumnName(), colList.get(0).getTableName()));
        }

        return pkList;
    }

    @Override
    public String getEntityPrimaryKeyProperties(String tableName) {
        return CamelCaseUtils.underlineToCamel(getTablePrimaryKey(tableName).get(0).getColumnName().toLowerCase());
    }

    @Override
    public String getDataType(String type) {
        String t = type.toUpperCase();
        switch (t) {
            case "VARCHAR":
            case "TEXT":
                return "String";
            case "CHAR":
                return "Character";
            case "BLOB":
                return "byte[]";
//            case "INT":
//                return "Long";
            case "BIGINT":
                return "BigInteger";
            case "INT":
            case "SMALLINT":
            case "MEDIUMINT":
                return "Integer";
            case "BIT":
            case "BOOLEAN":
            case "BOOL":
            case "TINYINT":
                return "Boolean";
            case "FLOAT":
                return "Float";
            case "DOUBLE":
                return "Double";
            case "DECIMAL":
                return "java.math.BigDecimal";

            case "DATETIME":
            case "TIMESTAMP":
            case "DATE":
                return "java.time.LocalDateTime";

            default:
                return "Object";
        }
    }

    /**
     * 准备好渲染到模板上的数据
     * @param tableName 数据库表名称
     * @return
     */
    @Override
    public Map<String,Object> prepareDataModel(String tableName){
        Map<String, Object> dataModel = new LinkedHashMap<String, Object>();
        dataModel.put("sourceTable", tableName);
        dataModel.put("sourceTableDesc", this.getTableSchema(tableName).getTableComment() == null ? this.getTableSchema(tableName).getTableName() : this.getTableSchema(tableName).getTableComment());
        dataModel.put("columns", getTableColumns(tableName));
        dataModel.put("primaryKey",getTablePrimaryKey(tableName));
        dataModel.put("primaryKeyProperties", getEntityPrimaryKeyProperties(tableName));

        dataModel.put("underlineToCamel", underlineToCamelTemplateMethod);
        dataModel.put("getDataType", getDataTypeTemplateMethod);
        dataModel.put("dataBaseType",DataBaseType.MYSQL);
        return dataModel;
    }

}